package com.hualude.roadsign.repository;

import com.hualude.roadsign.dto.DTRequestDto;
import com.hualude.roadsign.dto.RoadsignDto;
import org.hibernate.query.NativeQuery;
import org.hibernate.transform.Transformers;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.util.List;

/**
 * Created by shao-yikun on 2017/4/9.
 */
public class RoadSignRepositoryImpl {

    @PersistenceContext
    private EntityManager em;

    public List<RoadsignDto> getRoadSignCollect4DT(DTRequestDto dtRequestDto, Integer roadPoleId) throws Exception {
        String sql = "select rs.*, ur.userName "+
                "from tb_roadsign rs, tb_user ur " +
                "where rs.createUserId = ur.userId " +
                "and rs.roadPoleId = :roadPoleId order by rs.RoadSignInstallTime desc";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(RoadsignDto.class));
        query.setParameter("roadPoleId", roadPoleId);
        return query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
    }

    public List<RoadsignDto> getRoadSignAudit4DT(DTRequestDto dtRequestDto, Integer roadPoleId) throws Exception {
        String sql = "select rs.*, ur.userName "+
                "from tb_roadsign rs, tb_user ur " +
                "where rs.createUserId = ur.userId and roadSignIsComplete = 0 " +
                "and rs.roadPoleId = :roadPoleId order by rs.roadSignInstallTime desc";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(RoadsignDto.class));
        query.setParameter("roadPoleId", roadPoleId);
        return query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
    }

    public List<RoadsignDto> getRoadSignCollect(Integer roadPoleId) throws Exception {
        String sql = "select rs.roadSignId, rs.roadPoleId, rs.roadSignDirection, rs.roadSignType, rs.roadSignMeaning, " +
                "rs.roadSignShape, rs.roadSignSize, rs.roadSignPic, rs.roadSignMaterial, " +
                "rs.roadSignInstallType, rs.roadSignFixed, rs.roadSignSecurity, rs.roadSignBug, rs.orgId, " +
                "rs.createUserId, ur.userName, rs.roadSignInstallTime, rs.roadSignRemark, rs.mainOrgId, " +
                "rs.roadSignLight, rs.environment, rs.defectsLiabilityPeriod, rs.serviceLife, rs.installOrg,rs.roadSignIsComplete " +
                "from tb_roadsign rs, tb_user ur " +
                "where rs.createUserId = ur.userId " +
                "and rs.roadPoleId = :roadPoleId order by rs.RoadSignInstallTime desc";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(RoadsignDto.class));
        query.setParameter("roadPoleId", roadPoleId);
        return query.getResultList();
    }

}
